CREATE OR REPLACE FUNCTION add_movie(movie_info VARCHAR[], genres_param VARCHAR[], stars_info VARCHAR[][]) RETURNS VARCHAR AS $$
	DECLARE
		title_param VARCHAR;
		year_param INTEGER;
		director_rest_of_name_param VARCHAR;
		director_last_name_param VARCHAR;
		banner_url_param VARCHAR;
		trailer_url_param VARCHAR;

		max_movie_id INTEGER;
		max_genre_id INTEGER;
		max_star_id INTEGER;

		max_genres_in_movies_id INTEGER;
		max_stars_in_movies_id INTEGER;

		existing_genre_name VARCHAR;
		existing_genre_id INTEGER;

		existing_star_id INTEGER;
		star_last_name_param VARCHAR;
		star_first_name_param VARCHAR;
		star_birthday_param DATE;
		star_url_param VARCHAR;

		messageStringToReturn VARCHAR;
        BEGIN
		messageStringToReturn := '';
        
		title_param := movie_info[1];
		director_last_name_param := movie_info[2];
		director_rest_of_name_param := movie_info[3];
		year_param := CAST(movie_info[4] AS INTEGER);
		banner_url_param := movie_info[5];
		trailer_url_param := movie_info[6];
        
		SELECT max(movies.id) INTO max_movie_id FROM movies;                
                SELECT max(genres.id) INTO max_genre_id FROM genres;
                SELECT max(stars.id) INTO max_star_id FROM stars;
                SELECT max(stars_in_movies.id) INTO max_stars_in_movies_id FROM stars_in_movies;
                SELECT max(genres_in_movies.id) INTO max_genres_in_movies_id FROM genres_in_movies;

		max_movie_id := max_movie_id + 1;
                INSERT INTO movies (id, title, year, director_rest_of_name,
		director_last_name, banner_url, trailer_url)
		VALUES (max_movie_id, title_param, year_param, director_rest_of_name_param,
			director_last_name_param, banner_url_param, trailer_url_param);

		RAISE NOTICE '% was added with id %, year %, director % %, banner url: %, and trailer url: %', title_param,
				max_movie_id, year_param, director_rest_of_name_param, director_last_name_param, 
				banner_url_param, trailer_url_param;
		messageStringToReturn := messageStringToReturn || title_param || ' was added with id ' || max_movie_id 
					|| ', year ' || year_param || ', director ' || director_rest_of_name_param ||' ' 
					|| director_last_name_param || ', banner url: ' || banner_url_param 
					|| ', and trailer url: ' || trailer_url_param || '\n';

		IF genres_param[1] IS NOT NULL AND genres_param[1] NOT ILIKE '''''' THEN
			FOR i IN array_lower(genres_param, 1)..array_upper(genres_param, 1)
			LOOP
				SELECT genres.id INTO existing_genre_id FROM genres WHERE genres.name ILIKE genres_param[i];
				max_genres_in_movies_id := max_genres_in_movies_id + 1;

				IF existing_genre_id IS NULL THEN
					max_genre_id := max_genre_id + 1;
								
					INSERT INTO genres (id, name) VALUES (max_genre_id, genres_param[i]);
					INSERT INTO genres_in_movies (genre_id, movie_id, id) VALUES (max_genre_id, max_movie_id, max_genres_in_movies_id);

					RAISE NOTICE 'Genre % with id % inserted and linked with %', genres_param[i], max_genre_id, title_param;
					messageStringToReturn := messageStringToReturn || 'Genre ' || genres_param[i] || ' with id ' || max_genre_id || ' inserted and linked with ' || title_param || '\n';
					
				ELSE
					INSERT INTO genres_in_movies (genre_id, movie_id, id) VALUES (existing_genre_id, max_movie_id, max_genres_in_movies_id);

					RAISE NOTICE 'Genre % with id % linked with %', genres_param[i], existing_genre_id, title_param;
					messageStringToReturn := messageStringToReturn || 'Genre ' || genres_param[i] || ' with id ' || existing_genre_id || ' linked with ' || title_param || '\n';
				END IF;
			END LOOP;
		END IF;

		IF stars_info[1][1] IS NOT NULL THEN
			FOR i IN array_lower(stars_info, 1)..array_upper(stars_info, 1)
			LOOP
				SELECT id INTO existing_star_id FROM stars WHERE stars.first_name ILIKE stars_info[i][1] AND stars.last_name ILIKE stars_info[i][2];
				max_stars_in_movies_id := max_stars_in_movies_id + 1;

				IF existing_star_id IS NULL THEN
					max_star_id := max_star_id + 1;

					star_first_name_param := stars_info[i][1];
					star_last_name_param := stars_info[i][2];
					IF stars_info[i][3] ILIKE '''''' THEN
						--RAISE NOTICE 'Empty Birthday';
					ELSE
						--RAISE NOTICE 'The string being assigned as the birthdate is [%]', stars_info[i][3];
						star_birthday_param := CAST(stars_info[i][3] AS DATE);
					END IF;
					star_url_param := stars_info[i][4];

					INSERT INTO stars(id, first_name, last_name, dob, photo_url) 
						VALUES ( max_star_id, star_first_name_param, star_last_name_param, star_birthday_param, star_url_param );
					INSERT INTO stars_in_movies (star_id, movie_id, id) VALUES (max_star_id, max_movie_id, max_stars_in_movies_id);

					RAISE NOTICE 'Star % % with id %, birthdate %, and photo url: % inserted and linked with %', star_first_name_param, star_last_name_param,
						max_star_id, star_birthday_param, star_url_param, title_param;
					messageStringToReturn := messageStringToReturn || 'Star ' || star_first_name_param || ' ' 
								|| star_last_name_param || ' with id ' || max_star_id 
								|| ', birthdate ' || star_birthday_param || ', and photo url: ' 
								|| star_url_param || ' inserted and linked with ' || title_param || '\n';
				ELSE
					INSERT INTO stars_in_movies (star_id, movie_id, id) VALUES (existing_star_id, max_movie_id, max_stars_in_movies_id);

					RAISE NOTICE 'Star % % with id % linked with %', stars_info[i][1], stars_info[i][2],
						max_star_id, title_param;
					messageStringToReturn := messageStringToReturn || 'Star ' || stars_info[i][1] || ' ' || stars_info[i][2] || ' with id ' || max_star_id || ' linked with ' || title_param || '\n';
				END IF;
				 
			END LOOP;
		END IF;

		RETURN messageStringToReturn;
        END;
        $$
LANGUAGE 'plpgsql';
